#!/usr/bin/env python3
"""
为MySQL 8.0加载时区数据
"""

import pymysql
import requests
import tempfile
import os

# 数据库配置
DB_CONFIG = {
    'host': '192.168.37.11',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'mysql',
    'charset': 'utf8mb4'
}

def load_timezone_data():
    """加载MySQL时区数据"""
    try:
        print('为MySQL 8.0加载时区数据...')
        print('=' * 50)
        
        # 连接到mysql数据库
        connection = pymysql.connect(**DB_CONFIG)
        cursor = connection.cursor()
        
        print('✅ 连接到MySQL成功')
        
        # 手动插入一些常用的时区数据
        timezone_data = [
            # 插入时区
            "INSERT IGNORE INTO time_zone (Use_leap_seconds) VALUES ('N')",
            
            # 插入时区名称
            "INSERT IGNORE INTO time_zone_name (Name, Time_zone_id) VALUES ('Asia/Shanghai', 1)",
            "INSERT IGNORE INTO time_zone_name (Name, Time_zone_id) VALUES ('UTC', 2)",
            "INSERT IGNORE INTO time_zone_name (Name, Time_zone_id) VALUES ('Asia/Beijing', 1)",
            
            # 插入时区转换规则（简化版本，仅用于演示）
            "INSERT IGNORE INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES (1, 0, 28800, 'N', 'CST')",
            "INSERT IGNORE INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES (2, 0, 0, 'N', 'UTC')",
        ]
        
        print('正在插入时区数据...')
        for sql in timezone_data:
            try:
                cursor.execute(sql)
                print(f'✅ 执行: {sql[:50]}...')
            except Exception as e:
                print(f'⚠️ 跳过: {sql[:50]}... ({e})')
        
        connection.commit()
        print('✅ 时区数据插入完成')
        
        # 验证插入结果
        cursor.execute('SELECT COUNT(*) FROM time_zone_name')
        tz_count = cursor.fetchone()[0]
        print(f'时区名称记录数: {tz_count}')
        
        if tz_count > 0:
            cursor.execute("SELECT name FROM time_zone_name")
            zones = cursor.fetchall()
            print('可用时区:')
            for zone in zones:
                print(f'  {zone[0]}')
            
            # 测试 Asia/Shanghai
            try:
                cursor.execute("SET time_zone = 'Asia/Shanghai'")
                cursor.execute('SELECT @@session.time_zone, NOW()')
                result = cursor.fetchone()
                session_tz, current_time = result
                print(f'\n✅ Asia/Shanghai 测试成功')
                print(f'会话时区: {session_tz}')
                print(f'当前时间: {current_time}')
                return True
            except Exception as e:
                print(f'\n❌ Asia/Shanghai 测试失败: {e}')
                return False
        
        connection.close()
        return True
        
    except Exception as e:
        print(f'❌ 加载时区数据失败: {e}')
        return False

def download_official_timezone_data():
    """下载官方时区数据（备选方案）"""
    print('\n备选方案：下载官方时区数据')
    print('=' * 50)
    print('您可以从以下地址下载官方时区数据:')
    print('https://dev.mysql.com/downloads/timezones.html')
    print('')
    print('下载后，使用以下命令导入:')
    print('mysql -u root -p mysql < timezone_data.sql')

if __name__ == "__main__":
    success = load_timezone_data()
    
    if not success:
        download_official_timezone_data()
        print('\n如果上述方法都不可行，建议继续使用 +08:00 格式')
    else:
        print('\n🎉 时区数据加载成功！现在可以使用 Asia/Shanghai 时区了')
